Amazon Redshift ソートキーのテーブル最適化を有効化するビューを作成してみた

石川覚
2024.11.14
AWS事業本部コンサルティング部の石川です。Amazon Redshiftのテーブルのソートキーを「AUTO」に一括で自動設定に変更したり、元の設定に戻したりするためのSQLを生成するビューの作成しましたので紹介します。
 ソートキーを自動的に設定する機能とはAmazon Redshiftのテーブルのソートキーは、クエリの条件に基づいて、スキャンする必要のないブロックをスキップすることでパフォーマンスに重要な影響を与えます。従来は手動設定でしたが、現在は自動テーブル最適化（ATO）という機能があり、分散スタイルを自動的に設定します。
ATOは機械学習を用いて、クエリとテーブルの相互作用を監視し、クラスターのワークロードパフォーマンスを最適化します。ただし、この機能は各テーブルに個別に適用する必要があるため、一括で有効化したり元の設定に戻すためのSQLを生成するビューを作成しました。
 ソートキーのテーブル自動設定するためのビューテーブルのソートキーをバックアップするための DDL を取得するためのビューです。
ソート キーが設定されている既存のテーブルのソート キー設定をバックアップします。ソートキーが設定されているテーブルを後で元のソートキー設定に復元するための SQL を生成します。
v_generate_alter_sortkey_backup_ddl
--DROP VIEW admin.v_generate_alter_sortkey_backup_ddl;
/**********************************************************************************************
Purpose: View to retrieve the DDL for the sortkeys of existing tables.

Notes:   Backup the sortkey settings of existing tables with sortkeys configured.
         Generate SQL to restore tables with sortkeys to their original sortkey settings later.

         The following filters are useful:
           where tablename in ('t1', 't2')     -- only get DDL for specific tables
           where schemaname in ('s1', 's2')    -- only get DDL for specific schemas

History:
2024-11-14 Satoru Ishikawa Created
**********************************************************************************************/
CREATE OR REPLACE VIEW admin.v_generate_alter_sortkey_backup_ddl
AS
SELECT
 table_id
 ,REGEXP_REPLACE (schemaname, '^zzzzzzzz', '') AS schemaname
 ,REGEXP_REPLACE (tablename, '^zzzzzzzz', '') AS tablename
 ,seq
 ,ddl
FROM
 (
 SELECT
  table_id
  ,schemaname
  ,tablename
  ,seq
  ,ddl
 FROM
  (
  SELECT x.table_id::bigint as table_id, x.schemaname AS schemaname, x.tablename AS tablename, seq,
    case when x.min_sort <0 then 'ALTER TABLE ' || x.tablename || ' ALTER INTERLEAVED SORTKEY (' else 'ALTER TABLE ' || x.schemaname || '.' || x.tablename || ' ALTER SORTKEY (' end as ddl 
    FROM (SELECT
      c.oid::bigint as table_id
      ,n.nspname AS schemaname
      ,c.relname AS tablename
      ,499999999 AS seq
      ,min(attsortkeyord) min_sort 
      FROM pg_namespace AS n
      INNER JOIN  pg_class AS c ON n.oid = c.relnamespace
      INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
      WHERE c.relkind = 'r'
      AND abs(a.attsortkeyord) > 0
      AND a.attnum > 0
      group by 1,2,3,4
    ) AS x
  )
  UNION 
  (
    SELECT
   c.oid::bigint as table_id
   ,n.nspname AS schemaname
   ,c.relname AS tablename
   ,500000000 + abs(a.attsortkeyord) AS seq
   ,CASE WHEN abs(a.attsortkeyord) = 1
    THEN '\t' + QUOTE_IDENT(a.attname)
    ELSE '\t, ' + QUOTE_IDENT(a.attname)
    END AS ddl
  FROM  pg_namespace AS n
  INNER JOIN pg_class AS c ON n.oid = c.relnamespace
  INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
  WHERE c.relkind = 'r'
    AND abs(a.attsortkeyord) > 0
    AND a.attnum > 0
  ORDER BY abs(a.attsortkeyord))
  UNION SELECT
   c.oid::bigint as table_id
   ,n.nspname AS schemaname
   ,c.relname AS tablename
   ,599999999 AS seq
   ,'\t);' AS ddl
  FROM pg_namespace AS n
  INNER JOIN  pg_class AS c ON n.oid = c.relnamespace
  INNER JOIN  pg_attribute AS a ON c.oid = a.attrelid
  WHERE c.relkind = 'r'
    AND abs(a.attsortkeyord) > 0
    AND a.attnum > 0
 )
 ORDER BY table_id,schemaname, tablename, seq
;
実行結果の例
dev=# select * from v_generate_alter_sortkey_backup_ddl;
 table_id | schemaname  |                tablename                 |    seq    |                                 ddl                                  
----------+-------------+------------------------------------------+-----------+----------------------------------------------------------------------
   293976 | public      | category                                 | 499999999 | ALTER TABLE category ALTER SORTKEY (
   293976 | public      | category                                 | 500000001 |         catid
   293976 | public      | category                                 | 599999999 |         );
   293979 | public      | date                                     | 499999999 | ALTER TABLE date ALTER SORTKEY (
   293979 | public      | date                                     | 500000001 |         dateid
   293979 | public      | date                                     | 599999999 |         );
   293982 | public      | event                                    | 499999999 | ALTER TABLE event ALTER SORTKEY (
   293982 | public      | event                                    | 500000001 |         dateid
   293982 | public      | event                                    | 599999999 |         );
   293985 | public      | listing                                  | 499999999 | ALTER TABLE listing ALTER SORTKEY (
   293985 | public      | listing                                  | 500000001 |         dateid
   293985 | public      | listing                                  | 599999999 |         );
   293988 | public      | sales                                    | 499999999 | ALTER TABLE sales ALTER SORTKEY (
   293988 | public      | sales                                    | 500000001 |         dateid
   293988 | public      | sales                                    | 599999999 |         );
   293991 | public      | users                                    | 499999999 | ALTER TABLE users ALTER SORTKEY (
   293991 | public      | users                                    | 500000001 |         userid
   293991 | public      | users                                    | 599999999 |         );
   293994 | public      | venue                                    | 499999999 | ALTER TABLE venue ALTER SORTKEY (
   293994 | public      | venue                                    | 500000001 |         venueid
   293994 | public      | venue                                    | 599999999 |         );
   302174 | public      | part                                     | 499999999 | ALTER TABLE part ALTER SORTKEY (
   302174 | public      | part                                     | 500000001 |         p_partkey
   302174 | public      | part                                     | 599999999 |         );
   326738 | public      | testtab                                  | 499999999 | ALTER TABLE testtab ALTER SORTKEY (
   326738 | public      | testtab                                  | 500000001 |         catid
   326738 | public      | testtab                                  | 599999999 |         );
(27 rows)
 ソートキーのテーブル「AUTO」設定するためのビューテーブルのソートキーを「AUTO」に設定するための DDL を取得するためのビューです。
ソートキーが設定されている既存のテーブルのソート キー設定を「AUTO」に変更するための SQL を生成します。
v_generate_alter_sortkey_auto_ddl
--DROP VIEW admin.v_generate_alter_sortkey_auto_ddl;
/**********************************************************************************************
Purpose: View to retrieve the DDL for setting the sortkey of a table to "AUTO".

Notes:   Set the sortkey to "AUTO" for existing tables that already have sortkeys configured. 
         It is recommended to backup the existing sortkey settings so that you can revert to 
         the original configuration later if needed.

         The following filters are useful:
           where tablename in ('t1', 't2')     -- only get DDL for specific tables
           where schemaname in ('s1', 's2')    -- only get DDL for specific schemas

History:
2024-11-14 Satoru Ishikawa Created
**********************************************************************************************/
CREATE OR REPLACE VIEW public.v_generate_alter_sortkey_auto_ddl
AS
SELECT
 table_id
 ,REGEXP_REPLACE (schemaname, '^zzzzzzzz', '') AS schemaname
 ,REGEXP_REPLACE (tablename, '^zzzzzzzz', '') AS tablename
 ,seq
 ,ddl
FROM
 (
 SELECT
  table_id
  ,schemaname
  ,tablename
  ,seq
  ,ddl
 FROM
  (
  SELECT x.table_id::bigint as table_id, x.schemaname AS schemaname, x.tablename AS tablename, seq,
    case when x.min_sort <0 then 'ALTER TABLE ' || x.tablename || ' ALTER  SORTKEY AUTO;' else 'ALTER TABLE ' || x.tablename || ' ALTER SORTKEY AUTO;' end as ddl 
    FROM (SELECT
      c.oid::bigint as table_id
      ,n.nspname AS schemaname
      ,c.relname AS tablename
      ,499999999 AS seq
      ,min(attsortkeyord) min_sort 
      FROM pg_namespace AS n
      INNER JOIN  pg_class AS c ON n.oid = c.relnamespace
      INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
      WHERE c.relkind = 'r'
      AND abs(a.attsortkeyord) > 0
      AND a.attnum > 0
      group by 1,2,3,4
    ) AS x
  )
 )
 ORDER BY table_id,schemaname, tablename, seq
;
実行結果の例
dev=# select * from v_generate_alter_sortkey_auto_ddl;
 table_id | schemaname  |                tablename                 |    seq    |                                   ddl                                    
----------+-------------+------------------------------------------+-----------+--------------------------------------------------------------------------
   293976 | public      | category                                 | 499999999 | ALTER TABLE category ALTER SORTKEY AUTO;
   293979 | public      | date                                     | 499999999 | ALTER TABLE date ALTER SORTKEY AUTO;
   293982 | public      | event                                    | 499999999 | ALTER TABLE event ALTER SORTKEY AUTO;
   293985 | public      | listing                                  | 499999999 | ALTER TABLE listing ALTER SORTKEY AUTO;
   293988 | public      | sales                                    | 499999999 | ALTER TABLE sales ALTER SORTKEY AUTO;
   293991 | public      | users                                    | 499999999 | ALTER TABLE users ALTER SORTKEY AUTO;
   293994 | public      | venue                                    | 499999999 | ALTER TABLE venue ALTER SORTKEY AUTO;
   302174 | public      | part                                     | 499999999 | ALTER TABLE part ALTER SORTKEY AUTO;
   326738 | public      | testtab                                  | 499999999 | ALTER TABLE testtab ALTER SORTKEY AUTO;
(9 rows)

 最後に執筆時点では、各データブロックの最小値と最大値を記録したゾーンマップを使用したゾーンマッププルーニングですが、昨年のre:InventやSIGMODの論文の中で紹介されたWHERE句の条件に基づいて、スキャンする必要のない行やブロックを除外する述語プルーニングにも対応する予定ですので、テーブルのソートキーを「AUTO」に設定するのがニューノーマルと言えるでしょう。
Amazon Redshiftの自動テーブル最適化（ATO）は、クエリパフォーマンスを向上させるための重要な機能ですが、個々のテーブルに適用する必要があります。この課題に対処するため、二つのビューを作成しました。１つ目のビュー「v_generate_alter_sortkey_backup_ddl」は、既存のソートキー設定をバックアップし、後で元の設定に戻すためのSQLを生成します。２つ目のビュー「v_generate_alter_sortkey_auto_ddl」は、テーブルのソートキーを「AUTO」に設定するためのDDLを生成します。
これらのビューを使用することで、データベース管理者は複数のテーブルに対して一括でソートキーの自動設定を有効化したり、必要に応じて元の設定に戻したりすることが容易になります。この方法は、大規模なRedshiftクラスターの管理を効率化し、パフォーマンス最適化プロセスを簡素化する上で非常に有用です。ただし、変更を適用する前に、現在の設定をバックアップし、変更の影響を慎重に評価することが重要です。
 合わせて読みたいhttps://dev.classmethod.jp/articles/20201210-amazon-redshift-performance-tuning-automated/
https://dev.classmethod.jp/articles/20231128-amazon-redshift-multidimensional-data-layouts/
https://dev.classmethod.jp/articles/20241008-automated-multidimensional-data-layouts-in-amazon-redshift/